<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Messages</title>
</head>
<body>
<%@ page import="tuna.Process" %>
<%@ page import="tuna.DBConnection" %>
<%@ page import="java.sql.ResultSet;" %>
<%
	String day = request.getParameter("day");
	String us = request.getParameter("us");
	String role = request.getParameter("role");
	String id = request.getParameter("id");
	
	if(Process.validate(day, us, role, id)) {
		String parameters = "?day=" + day + "&us=" + us + "&role=" + role + "&id=" + id;
%>
	<table align="center" style="font-family: arial; width: 480px;">
	<tr>
		<td colspan=2>
			&nbsp;
		</td>
		<td>
			<input type="button" value="Compose Message" onclick="location.href='/tuna/composeMessage.jsp<%= parameters %>'"/>
		</td>
	</tr>
	<tr>
		<td>
			Sender			
		</td>
		<td>
			Time
		</td>
		<td>
			Lastest Message
		</td>
	</tr>
	<% 
		//Create DB connection & connect
	    DBConnection dbCon = new DBConnection("localhost:3306", "tuna", "root", "asdf1234");
	    dbCon.connectDB();
	    
	    //Retrieve result set
	    String query = "select latestData.id, latestData.senderId, latestData.receiverId, latestData.sentAt, " + 
	    "latestData.detail, userRecord.utorId as senderUtorId from tuna.userRecord inner join " + 
	    "(select id, senderId, receiverId, sentAt, detail from tuna.msgRecord natural join " +
		"(select senderId, max(sentAt) as sentAt from tuna.msgRecord where receiverId=" + id + " group by senderId) as latest " + 
		"where receiverId =" + id + ") as latestData on userRecord.id=latestData.senderId order by sentAt desc";
	    
	    ResultSet latestMsg = dbCon.runQuery(query);	
	    
	    while(latestMsg.next()) {
			String msgId = latestMsg.getString(1);
			String senderId = latestMsg.getString(2);
			String receiverId = latestMsg.getString(3);
			String sentAt = latestMsg.getString(4);
			String detail = latestMsg.getString(5);
			String senderUtorId = latestMsg.getString(6);
			
			String msg_param = parameters + "&senderId=" + senderId + "&senderUtorId=" + senderUtorId;
	%>
	<tr>
		<td>
			<a href="/tuna/composeMessage.jsp<%= msg_param %>"><%= senderUtorId %></a>
		</td>
		<td>		
			<%= sentAt %>
		</td>
		<td>
			<a href="/tuna/viewMessage.jsp<%= msg_param %>"><%= detail %></a>
		</td>
	</tr>
	<%
		}
	    dbCon.disconnectDB();
	%>
	</table>
<% 
	}
	else {
		response.sendRedirect("/tuna/login.jsp");
	}
%>
</body>
</html>